package com.yu.dv.service;

import com.yu.dv.autoconfigure.properties.NaturalLanguageSqlProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

@Slf4j
public class TextProcessor {

    private final DatabaseMetadataService databaseMetadataService;
    private final NaturalLanguageSqlProperties properties;

    public TextProcessor(DatabaseMetadataService databaseMetadataService, NaturalLanguageSqlProperties properties) {
        this.databaseMetadataService = databaseMetadataService;
        this.properties = properties;
    }

    public String preprocessInput(String naturalLanguage, String tableSchema) {
        if (StringUtils.isBlank(naturalLanguage)) {
            throw new IllegalArgumentException("自然语言输入不能为空");
        }
        return buildPrompt(naturalLanguage, tableSchema);
    }

    public String extractSqlFromResponse(String aiResponse) {
        if (StringUtils.isBlank(aiResponse)) {
            return "";
        }

        String cleaned = aiResponse.trim().replaceAll("```sql", "").replaceAll("```", "");
        String[] lines = cleaned.split("\n");
        StringBuilder sqlBuilder = new StringBuilder();

        for (String line : lines) {
            String trimmedLine = line.trim();
            if (isSqlKeyword(trimmedLine)) {
                sqlBuilder.append(trimmedLine).append(" ");
            }
        }

        String result = sqlBuilder.toString().trim();
        if (result.isEmpty()) {
            result = lines[0].trim();
        }
        return result;
    }

    private boolean isSqlKeyword(String line) {
        String upperLine = line.toUpperCase();
        return upperLine.startsWith("SELECT ") || upperLine.startsWith("INSERT ") ||
                upperLine.startsWith("UPDATE ") || upperLine.startsWith("DELETE ") ||
                upperLine.startsWith("CREATE ") || upperLine.startsWith("ALTER ") ||
                upperLine.startsWith("DROP ") || upperLine.startsWith("TRUNCATE ") ||
                upperLine.startsWith("RENAME ") || upperLine.startsWith("GRANT ") ||
                upperLine.startsWith("REVOKE ") || upperLine.startsWith("SHOW ") ||
                upperLine.startsWith("DESC ") || upperLine.startsWith("DESCRIBE ");
    }

    private String buildPrompt(String naturalLanguage, String tableSchema) {
        StringBuilder prompt = new StringBuilder();
        prompt.append("你是一个专业的SQL生成助手。请将自然语言描述转换为特定数据库的SQL语句。\n\n");

        // 明确告诉AI数据库类型
        String databaseType = databaseMetadataService.getDatabaseProductName();
        prompt.append("目标数据库: ").append(databaseType).append("\n\n");

        String databaseSchema = getDatabaseSchema();
        if (StringUtils.isNotBlank(databaseSchema)) {
            prompt.append("当前数据库结构信息：\n").append(databaseSchema).append("\n\n");
        }

        if (StringUtils.isNotBlank(tableSchema)) {
            prompt.append("用户提供的额外表结构信息：\n").append(tableSchema).append("\n\n");
        }

        prompt.append("自然语言描述：").append(naturalLanguage).append("\n\n");
        prompt.append("要求：\n");
        prompt.append("1. 只返回SQL语句，不要包含任何解释或额外文本\n");
        prompt.append("2. 确保SQL语法正确且符合").append(databaseType).append("数据库的规范\n");
        prompt.append("3. 支持所有类型的SQL（SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP等）\n");
        prompt.append("4. 生成的SQL应该基于上述数据库结构\n");
        prompt.append("5. 使用").append(databaseType).append("数据库特有的语法和特性\n\n");
        prompt.append("SQL语句：");
        return prompt.toString();
    }

    private String getDatabaseSchema() {
        if (!properties.isIncludeSchemaInPrompt()) {
            return "";
        }

        try {
            if (properties.isUseSimplifiedSchema()) {
                return databaseMetadataService.getSimplifiedDatabaseSchema();
            } else {
                return databaseMetadataService.getDatabaseSchemaAsText();
            }
        } catch (Exception e) {
            log.warn("获取数据库结构失败: {}", e.getMessage());
            return "无法获取数据库结构信息";
        }
    }
}